import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class TestUserMapping {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/lekang_db?useSSL=false&serverTimezone=UTC";
        String username = "root";
        String password = "123456";
        
        try (Connection conn = DriverManager.getConnection(url, username, password)) {
            // 查询护理组部长角色ID=3的用户
            String sql = "SELECT sur.user_id, su.user_name, su.nick_name, sur.role_id, sr.role_name " +
                        "FROM sys_user_role sur " +
                        "LEFT JOIN sys_user su ON sur.user_id = su.user_id " +
                        "LEFT JOIN sys_role sr ON sur.role_id = sr.role_id " +
                        "WHERE sur.role_id = 3 " +
                        "ORDER BY sur.user_id";
            
            try (PreparedStatement stmt = conn.prepareStatement(sql);
                 ResultSet rs = stmt.executeQuery()) {
                
                System.out.println("护理组部长角色(ID=3)的用户:");
                System.out.println("用户ID\t用户名\t昵称\t角色ID\t角色名");
                System.out.println("----------------------------------------");
                
                while (rs.next()) {
                    System.out.println(rs.getLong("user_id") + "\t" +
                                     rs.getString("user_name") + "\t" +
                                     rs.getString("nick_name") + "\t" +
                                     rs.getLong("role_id") + "\t" +
                                     rs.getString("role_name"));
                }
            }
            
            // 查询所有用户及其角色
            String sql2 = "SELECT su.user_id, su.user_name, su.nick_name, " +
                         "GROUP_CONCAT(sr.role_name) as roles " +
                         "FROM sys_user su " +
                         "LEFT JOIN sys_user_role sur ON su.user_id = sur.user_id " +
                         "LEFT JOIN sys_role sr ON sur.role_id = sr.role_id " +
                         "WHERE su.user_id IN (1, 2, 3, 4, 5) " +
                         "GROUP BY su.user_id, su.user_name, su.nick_name " +
                         "ORDER BY su.user_id";
            
            try (PreparedStatement stmt = conn.prepareStatement(sql2);
                 ResultSet rs = stmt.executeQuery()) {
                
                System.out.println("\n所有用户及其角色:");
                System.out.println("用户ID\t用户名\t昵称\t角色");
                System.out.println("----------------------------------------");
                
                while (rs.next()) {
                    System.out.println(rs.getLong("user_id") + "\t" +
                                     rs.getString("user_name") + "\t" +
                                     rs.getString("nick_name") + "\t" +
                                     rs.getString("roles"));
                }
            }
            
        } catch (SQLException e) {
            System.err.println("数据库连接失败: " + e.getMessage());
        }
    }
}
